# demo03_pymysql.py
import pymysql

conn = pymysql.connect(   # 1.创建连接
    host="127.0.0.1",  # 主机地址
    port=3306,         # 端口号, int
    user="root",       # 连接mysql服务的用户名
    db="tedu1",        # 操作的数据库
    charset="utf8"     # 指定字符集
)
cur = conn.cursor()  # 2.创建游标: 操作数据库的接口
# 3.数据库操作
# ===================3.1 建表===================
# create_bm = """create table bm(
#     bm_id int, bm_name varchar(50)
# );"""
# cur.execute(create_bm)
# ===================3.2 插入数据=================
# a.读取文件(/opt/bm.txt)
# b.使用pymysql将部门信息添加到bm表中
# insert_bm = "insert into bm values(%s, %s);"
# d_list = []
# with open("/opt/bm.txt", mode="r") as fr:
#     for line in fr.readlines()[1:]:
#         id, name = line[:-1].split(" ")  #[6,安保部]
#         # cur.execute(insert_bm, (id, name))  单条
#         d_list.append((id, name))
# cur.executemany(insert_bm, d_list)  # 批量



# insert_bm = "insert into bm values(%s, %s);"
# cur.execute(insert_bm, (1, "OPS"))  # 单条
# cur.executemany(insert_bm, [  # 批量
#     (2,"DEV"), (3,"TEST"), (4,"HR"), (5,"MARKET")
# ])
# =================3.3 查询数据=================
# select_bm = "select * from bm;"
# cur.execute(select_bm)
# print(cur.fetchone())  # 1
# print(cur.fetchmany(3))  # 接着向下抓取3条 234
# print(cur.fetchall())  # 抓取剩余的所有数据 5~9
# =================3.4 更新数据=================
update_bm="update bm set bm_name=%s where bm_id=%s;"
cur.execute(update_bm, ("运维部", 1))
# =================3.5 删除数据=================
delete_bm = "delete from bm where bm_id=%s;"
cur.executemany(delete_bm, [5, 6, 7, 8])
# 4.提交事务(保证写操作成功)以及关闭资源
conn.commit()  # 提交事务
cur.close()
conn.close()
